package com.zhiyou100.www.bbs.dao;

import com.zhiyou100.www.bbs.entityclass.TableTitle;
import com.zhiyou100.www.bbs.entityclass.TableUser;
import com.zhiyou100.www.bbs.function.jdbctool.C3P0_Util;

import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;

/**
 * @packageName: TableTitle
 * @className: com.zhiyou100.www.bbs.dao
 * @Description: TODO 话题类 dao
 * @author: yang
 * @date: 2020/6/4
 */
public class TableTitleDao {

    /**
     * @Description: TODO 根据 list 输出 table 字符串
     * @name: getTable
     * @param: [into]
     * @return: java.lang.String
     * @date: 6/9/20 14:55
     * @author: yang
     */
    public String getTable(ArrayList<TableTitle> into) {
        StringBuilder message = new StringBuilder();
        for (TableTitle tableTitle : into) {
            String tableHead = tableTitle.getTableHead();
            // 获取标题
            Integer tableId = tableTitle.getUserId();
            // 获取用户 id
            String userName = new TableUserDao().getOne(tableId).getUserRealName();
            // 获取作者的真实 姓名
            String tableCreateTime = tableTitle.getTableCreateTime();
            // 获取时间
            Integer tableScore = tableTitle.getTableScore();
            // 获取关注度
            String tableContent = tableTitle.getTableContent();
            // 获取内容
            message.append("<table style=\"width:910px;\">\n" + "    <tr>\n" + "        <td>标题：").append(tableHead).append("</td>\n").append("        <td>作者：").append(userName).append("</td>\n").append("        <td>时间：").append(tableCreateTime).append("</td>\n").append("        <td>关注度：").append(tableScore).append("</td>\n").append("    </tr>\n").append("    <tr>\n").append("        <td style=\"text-align: center;\">内容</td>\n").append("        <td colspan=\"3\">").append(tableContent).append("</td>\n").append("    </tr>\n").append("</table>").append("<br>");
        }

        return message.toString();
    }

    /**
     * @Description: TODO 获取所有话题，根据关注度 getTableScore()  ，从大到小排列
     * @name: getAll
     * @param: []
     * @return: java.util.ArrayList<com.zhiyou100.www.bbs.dao.TableTitle>
     * @date: 2020/6/6 8:56 下午
     * @author: yang
     */
    public ArrayList<TableTitle> getAll() {
        ArrayList<TableTitle> tableTitles = new ArrayList<>();
        // 创建一个 tableTitles 集合，用来放置所有的 TableTitle
        Connection connection = C3P0_Util.getConnection();
        // 获取链接
        String sql = "select * from tableTitle";
        // select * from tableTitle;
        // 准备 slq 模版
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            // 发送模版
            ResultSet resultSet = statement.executeQuery();
            // 获取结果集
            while (resultSet.next()) {
                // 处理结果集
                int tableId = resultSet.getInt(1);
                int userId = resultSet.getInt(2);
                String tableCreateTime = resultSet.getString(3);
                String tableHead = resultSet.getString(4);
                String tableContent = resultSet.getString(5);
                int tableScore = resultSet.getInt(6);
                int tableLikeNumber = resultSet.getInt(7);
                int tableDisLikeNumber = resultSet.getInt(8);
                TableTitle tableTitle = new TableTitle(tableId, userId, tableCreateTime, tableHead, tableContent, tableScore, tableLikeNumber, tableDisLikeNumber);
                // 创建 TableTitle 类
                tableTitles.add(tableTitle);
                // 添加到 集合
            }
            C3P0_Util.close(connection, statement, resultSet);
            // 关闭链接

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        tableTitles.sort(new Comparator<TableTitle>() {
            // 按照 getTableScore()  关注度 ，从大到小排列
            @Override
            public int compare(TableTitle o1, TableTitle o2) {
                if (o1.getTableScore() > o2.getTableScore()) {
                    return -1;
                } else {
                    return 0;
                }
            }
        });

        return tableTitles;
    }

    /**
     * @Description: TODO 根据 userId 该用户所有的的话题
     * @name: getOne
     * @param: [userId]
     * @return: com.zhiyou100.www.bbs.entityclass.TableTitle
     * @date: 2020/6/7 10:00 上午
     * @author: yang
     */
    public ArrayList<TableTitle> getOne(int userInId) {
        ArrayList<TableTitle> tableTitles = new ArrayList<>();
        // 创建一个 tableTitles
        Connection connection = C3P0_Util.getConnection();
        // 获取链接
        String sql = "select * from tableTitle where userId=?";
        // select * from tableTitle where userId=1;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            // 发送模版
            statement.setInt(1, userInId);
            // 设置参数
            ResultSet resultSet = statement.executeQuery();
            // 获取结果集
            while (resultSet.next()) {
                // 处理结果集
                int tableId = resultSet.getInt(1);
                int userId = resultSet.getInt(2);
                String tableCreateTime = resultSet.getString(3);
                String tableHead = resultSet.getString(4);
                String tableContent = resultSet.getString(5);
                int tableScore = resultSet.getInt(6);
                int tableLikeNumber = resultSet.getInt(7);
                int tableDisLikeNumber = resultSet.getInt(8);
                TableTitle tableTitle = new TableTitle(tableId, userId, tableCreateTime, tableHead, tableContent, tableScore, tableLikeNumber, tableDisLikeNumber);
                // 创建 TableTitle 类
                tableTitles.add(tableTitle);
                // 添加到 集合
            }
            C3P0_Util.close(connection, statement, resultSet);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tableTitles;
    }

    /**
     * @Description: TODO 话题搜索
     * @name: topicSearch
     * @param: [topic]
     * @return: java.util.ArrayList<com.zhiyou100.www.bbs.entityclass.TableTitle>
     * @date: 2020/6/7 3:32 下午
     * @author: yang
     */
    public ArrayList<TableTitle> topicSearch(String topic) {
        ArrayList<TableTitle> tableTitles = new ArrayList<>();
        // 创建一个 tableTitles
        Connection connection = C3P0_Util.getConnection();
        // 获取链接
        String sql = "select * from tableTitle where tableHead like ? or tableContent like ?;";
        // select * from tableTitle where tableHead like '%们%' or tableContent like '%们%';
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            // 发送模版
            statement.setString(1, "%" + topic + "%");
            statement.setString(2, "%" + topic + "%");
            // 设置参数
            ResultSet resultSet = statement.executeQuery();
            // 获取结果集
            while (resultSet.next()) {
                // 处理结果集
                int tableId = resultSet.getInt(1);
                int userId = resultSet.getInt(2);
                String tableCreateTime = resultSet.getString(3);
                String tableHead = resultSet.getString(4);
                String tableContent = resultSet.getString(5);
                int tableScore = resultSet.getInt(6);
                int tableLikeNumber = resultSet.getInt(7);
                int tableDisLikeNumber = resultSet.getInt(8);
                TableTitle tableTitle = new TableTitle(tableId, userId, tableCreateTime, tableHead, tableContent, tableScore, tableLikeNumber, tableDisLikeNumber);
                // 创建 TableTitle 类
                tableTitles.add(tableTitle);
                // 添加到 集合
            }
            C3P0_Util.close(connection, statement, resultSet);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tableTitles;
    }

    /**
     * @Description: TODO 添加话题
     * @name: addTopic
     * @param: []
     * @return: void
     * @date: 6/12/20 20:57
     * @author: yang
     */
    public void addTopic(TableUser user, String tableHead, String tableContent) {
        Connection connection = C3P0_Util.getConnection();
        // 获取链接
        String sql = "insert into tableTitle (userid, tableCreateTime, tableHead, tableContent) values (?, ?, ?, ?)";
        // insert into tableTitle (userid, tableCreateTime, tableHead, tableContent)
        //values (1, now(), 'hello', 'hei,daofdjsaof');
        // 准备sql
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            // 发送sql，获取模版
            int id = (new TableUserDao()).getId(((TableUser) (user)).getUserName(), ((TableUser) (user)).getUserPwd());
            statement.setInt(1, id);
            statement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
            statement.setString(3, tableHead);
            statement.setString(4, tableContent);
            int i = statement.executeUpdate();
            // 执行
            C3P0_Util.close(connection, statement, null);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void main(String[] args) {
        TableTitleDao tableTitleDao = new TableTitleDao();
        TableUser tableUser = new TableUser();
        tableUser.setUserName("1");
        tableUser.setUserPwd("1");
        int id = new TableUserDao().getId(tableUser.getUserName(), tableUser.getUserPwd());
        String head = "测试1";
        String tableContent = "话题1，话题1话题1，话题1话题1，话题1话题1，话题1话题1，话题1话题1，话题1话题1，话题1话题1，话题1";
        tableTitleDao.addTopic(tableUser, head, tableContent);


    }
}
